package org.database.generator.code;

import org.database.generator.code.model.TableColumn;
import org.database.generator.code.utils.Configuration;
import org.database.generator.code.utils.DBUtils;
import org.database.generator.code.utils.StringUtil;
import org.database.generator.code.model.Table;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author liangsu
 * @version v1.0
 * @Description
 * @Date 2019/8/27 11:14
 * @since
 */
public class TableInspector {
    private String queryTableCommentSQL = "select table_comment from information_schema.TABLES where TABLE_SCHEMA= ? and TABLE_NAME= ?";
    private String queryColumnSQL = "select column_name, column_type, column_comment, column_default  from information_schema.columns where table_schema = ? and table_name = ?";
    private String queryTableSQL = "select table_name, table_comment from information_schema.TABLES where TABLE_SCHEMA = ? ";

    private static Map<String, String> jdbcTypeToJavaTypeMap;
    private static Map<String, String> importMap;
    private static Map<String, String> filedAnnotationMap;
    private static Set<String> filteSet; // 过滤掉的字段

    static{
        jdbcTypeToJavaTypeMap = new HashMap<>();
        jdbcTypeToJavaTypeMap.put("varchar", "String");
        jdbcTypeToJavaTypeMap.put("char", "String");
        jdbcTypeToJavaTypeMap.put("tinyint", "Integer");
        jdbcTypeToJavaTypeMap.put("bigint", "Long");
        jdbcTypeToJavaTypeMap.put("json", "JSONObject");
        jdbcTypeToJavaTypeMap.put("double", "Double");
        jdbcTypeToJavaTypeMap.put("int", "Integer");
        jdbcTypeToJavaTypeMap.put("longblob", "Byte[]");
        jdbcTypeToJavaTypeMap.put("bit", "Boolean");
        jdbcTypeToJavaTypeMap.put("float", "Float");
        jdbcTypeToJavaTypeMap.put("text", "String");
        jdbcTypeToJavaTypeMap.put("timestamp", "Timestamp");
        jdbcTypeToJavaTypeMap.put("datetime", "Timestamp");
        jdbcTypeToJavaTypeMap.put("date", "Date");
        jdbcTypeToJavaTypeMap.put("time", "Time");

        //
        importMap = new HashMap<>();
        importMap.put("Date", "java.sql.Date");
        importMap.put("Timestamp", "java.sql.Timestamp");
        importMap.put("Time", "java.sql.Time");
        importMap.put("JSONObject", "com.alibaba.fastjson.JSONObject");

        filteSet = new HashSet<>();
        filteSet.add("id");
        filteSet.add("sign");

        //
        filedAnnotationMap = new HashMap<>();
        filedAnnotationMap.put("deleted", "@TableLogic @TableField(fill = FieldFill.INSERT)");
        filedAnnotationMap.put("createTime", "@TableField(fill = FieldFill.INSERT)");
        filedAnnotationMap.put("updateTime", "@TableField(fill = FieldFill.INSERT_UPDATE)");
    }

    public Table queryTable(String tableName){
        String schema = Configuration.getDBName();
        Connection conn = DBUtils.getConnection();

        Table table = queryTable(conn, schema, tableName);
        table.setTableColumns(queryColumns(conn, schema, tableName));
        DBUtils.close(conn);

        populateAndFilter(table);

        return table;
    }

    public List<Table> queryTables4Html(String schema){
        Connection conn = DBUtils.getConnection();

        List<Table> tables = queryTables(conn, schema);

        tables = mergeShardingTable(tables);

        tables.forEach(t ->{
            if(t.getShardings().size() > 0){
                t.setTableColumns(queryColumns(conn, schema,
                        t.getTableName() + "_" + t.getShardings().get(0)));
            }else{
                t.setTableColumns(queryColumns(conn, schema, t.getTableName()));
            }
        });
        DBUtils.close(conn);

        return tables;
    }

    /**
     * 合并分片表
     * @param tables
     */
    private List<Table> mergeShardingTable(List<Table> tables) {
        Map<String, Table> shardTableMap = new HashMap<>(); // 分片表
        Set<Table> commonTables = new HashSet<>(); // 普通表

        for (Table table : tables) {
            String tableName = table.getTableName();
            int pos = tableName.lastIndexOf("_");
            boolean isShardingTable = isShardingTable(tableName);

            if(isShardingTable){
                String originTableName = tableName.substring(0, pos);
                String sharding = tableName.substring(pos + 1);
                Table tab = shardTableMap.get(originTableName);
                if(tab == null){
                    tab = table;
                    shardTableMap.put(originTableName, tab);
                    tab.setTableName(originTableName);
                }
                tab.getShardings().add(sharding);
            }else{
                commonTables.add(table); // 加入普通表
            }
        }

        // 构建返回结果
        List<Table> resTables = new ArrayList<>(shardTableMap.values());
        commonTables.forEach(t -> {
            // 当某一张表，既有分片表，又有非分片表的时候，以分片表为主，舍去非分片表
            // 如：既有 user、user_01，则舍去user表
            if(shardTableMap.get(t.getTableName()) == null){
                resTables.add(t);
            }else{
                System.out.println("存在分片表，舍去普通表：" + t.getTableName());
            }
        });
        return resTables;
    }

    private boolean isShardingTable(String tableName){
        int pos = tableName.lastIndexOf("_");
        if(pos < 0){
            return false;
        }
//        String reg = "[0-9]{2}";
        String reg = "[0-9]+";
        Pattern pattern = Pattern.compile(reg);
        String nameEnd = tableName.substring(pos);
        return pattern.matcher(nameEnd).find();
    }

    private void populateAndFilter(Table table){
        // 类名
        table.setClassName(StringUtil.lineToClassName(table.getTableName()));
        table.setClassNameLower(StringUtil.lineToHump(table.getTableName()));

        table.getTableColumns().forEach(c -> {
            // 字段属性名称
            c.setFieldName(StringUtil.lineToHump(c.getName()));
            c.setFieldNameUpper(StringUtil.lineToClassName(c.getName()));
            // jdbc类型转java类型
            c.setJavaType(jdbcTypeToJavaType(c.getJdbcType(), table));

            // 字段注解
            StringBuilder annoBuilder = new StringBuilder();
            String tableLogic = filedAnnotationMap.get(c.getFieldName());
            if(tableLogic != null){
                annoBuilder.append(tableLogic);
            }
            if(StringUtil.containsNumber(c.getName())){ // 包含有数字
                if(annoBuilder.length() > 0){
                    annoBuilder.append("\n");
                }
                annoBuilder.append("@TableField(value = \""+c.getName()+"\")");
            }

            // 设置注解
            String anno = annoBuilder.toString();
            if(anno != null && !"".equals(anno.trim())){
                c.setAnnoStr(anno);
            }
        });

        // 过滤字段
        List<TableColumn> tableColumns = table.getTableColumns().stream().filter(c -> {
            return !filteSet.contains(c.getFieldName());
        }).collect(Collectors.toList());
        table.setTableColumns(tableColumns);
    }

    private static String jdbcTypeToJavaType(String jdbcType, Table table){
        String type = jdbcType;
        int length = 0;
        int pos = jdbcType.indexOf("(");
        if(pos > -1){
            type = jdbcType.substring(0, pos);
            String num = jdbcType.substring(pos + 1, jdbcType.indexOf(")"));
            length = Integer.parseInt(num);
        }

        String javaType = jdbcTypeToJavaTypeMap.get(type);

        String importStr = importMap.get(javaType);
        if(importStr != null){
            table.getImports().add(importStr);
        }

        return javaType;
    }

    /**
     * 查询某个schemal下的所有的表名
     * @param conn
     * @param schema
     * @return
     */
    private List<Table> queryTables(Connection conn, String schema){
        List<Table> tables = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(queryTableSQL);
            ps.setString(1, schema);

            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Table table = new Table();
                table.setTableName(rs.getString("table_name"));
                table.setComment(rs.getString("table_comment"));
                tables.add(table);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }

    private Table queryTable(Connection conn, String schema, String tableName){
        Table table = null;
        try {
            PreparedStatement ps = conn.prepareStatement(queryTableCommentSQL);
            ps.setString(1, schema);
            ps.setString(2, tableName);

            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                table = new Table();
                table.setTableName(tableName);
                table.setComment(rs.getString("table_comment"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return table;
    }

    private List<TableColumn> queryColumns(Connection conn, String schema, String tableName){
        List<TableColumn> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(queryColumnSQL);
            ps.setString(1, schema);
            ps.setString(2, tableName);
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                TableColumn tc = new TableColumn();
                tc.setName(rs.getString("column_name"));
                tc.setJdbcType(rs.getString("column_type"));
                tc.setComment(rs.getString("column_comment"));
                tc.setColumnDefault(rs.getString("column_default"));
                list.add(tc);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
